﻿using System;
using Entities;
using System.Data;
namespace DataAccessLayer
{
    public class HoaDonBanSachDAL
    {
        QLNhaSach.DataService ds = new QLNhaSach.DataService();
        public HoaDonBanSachDAL()
        {
        }
        public DataTable layDanhSachHoaDon()
        {
            DataTable DT = new DataTable();
            try
            {
                string sql = "SELECT * FROM HOADONBANSACH";
                DT = ds.GetTable(sql);
            }
            catch (Exception)
            {
                throw;
            }
            return DT;
        }
        public String GetLastID()
        {
            String kq = null;
            try
            {
                string sql = "SELECT max(HDBS_MaHoaDon) FROM HoaDonBanSach";
                kq = (String)ds.ExcuteQuery(sql);
                ds.Close();
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }

        public int InsertHoaDon(HoaDon hd)
        {
            int kq = 0;
            try
            {
                String sql = String.Format("INSERT INTO HoaDonBanSach(HDBS_MaKhachHang,HDBS_NgayLapHoaDon) VALUES ({0},'{1}')",hd.MaKhachHang,hd.NgayLapHoaDon);
                kq = ds.ExcuteInsert(sql);
                ds.Close();
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
        public double GetTongTienCuaKhachHangMuaTrongThang(string thang, string nam, int maKhachHang)
        {
            double kq = 0;
            try
            {
                String sql = "SELECT SUM(CTHD_SOLUONGBAN*CTHD_DONGIABAN) FROM HOADONBANSACH, CHITIETHOADON, KHACHHANG WHERE";
                sql += " CTHD_MAHOADON = HDBS_MAHOADON AND";
                sql += " HDBS_MAKHACHHANG = KH_MAKHACHHANG AND";
                sql += " HDBS_MAKHACHHANG = " + maKhachHang.ToString() + " AND";
                sql += " YEAR(HDBS_NGAYLAPHOADON)='" + nam + "' AND MONTH(HDBS_NGAYLAPHOADON) ='" + thang + "'";
                kq = double.Parse(ds.ExcuteQuery(sql).ToString());
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
        public int DeleteHoaDon(string maHoaDon)
        {
            int kq = 0;
            try
            {
                String sql = "DELETE FROM HOADONBANSACH WHERE HDBS_MAHOADON=" + maHoaDon;
                kq = int.Parse(ds.ExcuteInsert(sql).ToString());
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
    }
}
